-- Drop Existing Procedures

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_Insert]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_Insert]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_Update]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_Update]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_InsertUpdate]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_InsertUpdate]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_Delete]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_Delete]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_DeleteDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_DeleteDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_Load]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_Load]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_SelectDynamic]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_SelectDynamic]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_SelectAll]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_SelectAll]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_SelectBy_MaKho]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_SelectBy_MaKho]

IF OBJECT_ID(N'[dbo].[p_WM_PhieuGD_DeleteBy_MaKho]') IS NOT NULL
	DROP PROCEDURE [dbo].[p_WM_PhieuGD_DeleteBy_MaKho]


GO


------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_Insert]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_Insert]
	@MaPhieu varchar(50),
	@LoaiPhieu char(1),
	@NgayNhap datetime,
	@MaKho varchar(10),
	@TongTien decimal(18, 0),
	@Status int,
	@GhiChu nvarchar(max),
	@ID bigint OUTPUT
AS

INSERT INTO [dbo].[t_WM_PhieuGD]
(
	[MaPhieu],
	[LoaiPhieu],
	[NgayNhap],
	[MaKho],
	[TongTien],
	[Status],
	[GhiChu]
)
VALUES 
(
	@MaPhieu,
	@LoaiPhieu,
	@NgayNhap,
	@MaKho,
	@TongTien,
	@Status,
	@GhiChu
)

SET @ID = SCOPE_IDENTITY()

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_Update]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_Update]
	@ID bigint,
	@MaPhieu varchar(50),
	@LoaiPhieu char(1),
	@NgayNhap datetime,
	@MaKho varchar(10),
	@TongTien decimal(18, 0),
	@Status int,
	@GhiChu nvarchar(max)
AS

UPDATE
	[dbo].[t_WM_PhieuGD]
SET
	[MaPhieu] = @MaPhieu,
	[LoaiPhieu] = @LoaiPhieu,
	[NgayNhap] = @NgayNhap,
	[MaKho] = @MaKho,
	[TongTien] = @TongTien,
	[Status] = @Status,
	[GhiChu] = @GhiChu
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_InsertUpdate]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_InsertUpdate]
	@ID bigint,
	@MaPhieu varchar(50),
	@LoaiPhieu char(1),
	@NgayNhap datetime,
	@MaKho varchar(10),
	@TongTien decimal(18, 0),
	@Status int,
	@GhiChu nvarchar(max)
AS
IF EXISTS(SELECT [ID] FROM [dbo].[t_WM_PhieuGD] WHERE [ID] = @ID)
	BEGIN
		UPDATE
			[dbo].[t_WM_PhieuGD] 
		SET
			[MaPhieu] = @MaPhieu,
			[LoaiPhieu] = @LoaiPhieu,
			[NgayNhap] = @NgayNhap,
			[MaKho] = @MaKho,
			[TongTien] = @TongTien,
			[Status] = @Status,
			[GhiChu] = @GhiChu
		WHERE
			[ID] = @ID
	END
ELSE
	BEGIN
		
		INSERT INTO [dbo].[t_WM_PhieuGD]
		(
			[MaPhieu],
			[LoaiPhieu],
			[NgayNhap],
			[MaKho],
			[TongTien],
			[Status],
			[GhiChu]
		)
		VALUES 
		(
			@MaPhieu,
			@LoaiPhieu,
			@NgayNhap,
			@MaKho,
			@TongTien,
			@Status,
			@GhiChu
		)		
	END
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_Delete]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_Delete]
	@ID bigint
AS

DELETE FROM 
	[dbo].[t_WM_PhieuGD]
WHERE
	[ID] = @ID

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_DeleteBy_MaKho]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_DeleteBy_MaKho]
	@MaKho varchar(10)
AS

DELETE FROM [dbo].[t_WM_PhieuGD]
WHERE
	[MaKho] = @MaKho

GO

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_DeleteDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_DeleteDynamic]
	@WhereCondition NVARCHAR(500)
AS

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 'DELETE FROM [dbo].[t_WM_PhieuGD] WHERE ' + @WhereCondition

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_Load]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_Load]
	@ID bigint
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[MaPhieu],
	[LoaiPhieu],
	[NgayNhap],
	[MaKho],
	[TongTien],
	[Status],
	[GhiChu]
FROM
	[dbo].[t_WM_PhieuGD]
WHERE
	[ID] = @ID
GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_SelectBy_MaKho]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_SelectBy_MaKho]
	@MaKho varchar(10)
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[MaPhieu],
	[LoaiPhieu],
	[NgayNhap],
	[MaKho],
	[TongTien],
	[Status],
	[GhiChu]
FROM
	[dbo].[t_WM_PhieuGD]
WHERE
	[MaKho] = @MaKho

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_SelectDynamic]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_SelectDynamic]
	@WhereCondition NVARCHAR(500),
	@OrderByExpression NVARCHAR(250) = NULL
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

DECLARE @SQL NVARCHAR(MAX)

SET @SQL = 
'SELECT 
	[ID],
	[MaPhieu],
	[LoaiPhieu],
	[NgayNhap],
	[MaKho],
	[TongTien],
	[Status],
	[GhiChu]
FROM [dbo].[t_WM_PhieuGD] 
WHERE ' + @WhereCondition

IF @OrderByExpression IS NOT NULL AND LEN(@OrderByExpression) > 0
BEGIN
	SET @SQL = @SQL + ' ORDER BY ' + @OrderByExpression
END

EXEC sp_executesql @SQL

GO

------------------------------------------------------------------------------------------------------------------------
-- Stored procedure name: [dbo].[p_WM_PhieuGD_SelectAll]
-- Database: Warehouse_Management
-- Author: Ngo Thanh Tung
-- Time created: Sunday, November 04, 2012
------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[p_WM_PhieuGD_SelectAll]
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
	[ID],
	[MaPhieu],
	[LoaiPhieu],
	[NgayNhap],
	[MaKho],
	[TongTien],
	[Status],
	[GhiChu]
FROM
	[dbo].[t_WM_PhieuGD]	

GO

